using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Configuration.Provider;
using System.Security.Permissions;
using System.Web;
using System.Web.Configuration;
using System.Web.Security;
using System.Web.UI;
using System.Web.Caching;

namespace XZ.Logistics.WebShop
{
    [SqlClientPermission(SecurityAction.Demand, Unrestricted = true)]
    public class SqlWebShopProvider : WebShopProvider
    {
        private string _applicationName;
        private string _connectionString;

        public override string ApplicationName
        {
            get { return _applicationName; }
            set { _applicationName = value; }
        }

        public string ConnectionString//Name
        {
            get { return _connectionString; }
            set { _connectionString = value; }
        }

        public override void Initialize(string name, NameValueCollection config)
        {
            // Verify that config isn't null
            if (config == null)
                throw new ArgumentNullException("config");

            // Assign the provider a default name if it doesn't have one
            if (String.IsNullOrEmpty(name))
                name = "SqlShopProvider";

            // Add a default "description" attribute to config if the
            // attribute doesn't exist or is empty
            if (string.IsNullOrEmpty(config["description"]))
            {
                config.Remove("description");
                config.Add("description", "SQL web shop provider");
            }

            // Call the base class's Initialize method
            base.Initialize(name, config);

            // Initialize _applicationName
            _applicationName = config["applicationName"];

            if (string.IsNullOrEmpty(_applicationName))
                _applicationName = "/";

            config.Remove("applicationName");

            // Initialize _connectionString
            string connect = config["connectionStringName"];

            if (String.IsNullOrEmpty(connect))
                throw new ProviderException("Empty or missing connectionStringName");

            config.Remove("connectionStringName");

            if (WebConfigurationManager.ConnectionStrings[connect] == null)
                throw new ProviderException("Missing connection string");

            _connectionString = WebConfigurationManager.ConnectionStrings[connect].ConnectionString;

            if (String.IsNullOrEmpty(_connectionString))
                throw new ProviderException("Empty connection string");

            // Throw an exception if unrecognized attributes remain
            if (config.Count > 0)
            {
                string attr = config.GetKey(0);
                if (!String.IsNullOrEmpty(attr))
                    throw new ProviderException("Unrecognized attribute: " + attr);
            }
        }
        /// <summary>
        /// Returns a collection with all the categories
        /// </summary>
        public override List<Category> GetCategories()
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_GetCategories", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cn.Open();
                return GetCategoryCollectionFromReader(ExecuteReader(cmd));
            }
        }

        /// <summary>
        /// Returns an existing department with the specified ID
        /// </summary>
        public override Category GetCategoryById(Guid categoryId)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_GetCategoryById", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@CategoryId", SqlDbType.UniqueIdentifier).Value = categoryId;
                cn.Open();
                IDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
                if (reader.Read())
                    return GetCategoryFromReader(reader);
                else
                    return null;
            }
        }

        /// <summary>
        /// Deletes a department
        /// </summary>
        public override bool DeleteCategory(Guid categoryId)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_DeleteCategory", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@CategoryId", SqlDbType.UniqueIdentifier).Value = categoryId;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        /// <summary>
        /// Updates a department
        /// </summary>
        public override bool UpdateCategory(Category category)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_UpdateCategory", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@CategoryId", SqlDbType.UniqueIdentifier).Value = category.Id;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = category.Title;
                cmd.Parameters.Add("@Importance", SqlDbType.Int).Value = category.Importance;
                cmd.Parameters.Add("@ImageUrl", SqlDbType.NVarChar).Value = category.ImageUrl;
                cmd.Parameters.Add("@Description", SqlDbType.NVarChar).Value = category.Description;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        /// <summary>
        /// Creates a new Category
        /// </summary>
        public override Guid InsertCategory(Category category)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_InsertCategory", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = category.AddedDate;
                cmd.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = category.AddedBy;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = category.Title;
                cmd.Parameters.Add("@Importance", SqlDbType.Int).Value = category.Importance;
                cmd.Parameters.Add("@ImageUrl", SqlDbType.NVarChar).Value = category.ImageUrl;
                cmd.Parameters.Add("@Description", SqlDbType.NVarChar).Value = category.Description;
                cmd.Parameters.Add("@CategoryId", SqlDbType.UniqueIdentifier).Direction = ParameterDirection.Output;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (Guid)cmd.Parameters["@CategoryId"].Value;
            }
        }

        /// <summary>
        /// Returns a collection with all the order statuses
        /// </summary>
        public override List<OrderStatus> GetOrderStatuses()
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_GetOrderStatuses", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cn.Open();
                return GetOrderStatusCollectionFromReader(ExecuteReader(cmd));
            }
        }

        /// <summary>
        /// Returns an existing order status with the specified ID
        /// </summary>
        public override OrderStatus GetOrderStatusById(int orderStatusId)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_GetOrderStatusById", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@OrderStatusId", SqlDbType.Int).Value = orderStatusId;
                cn.Open();
                IDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
                if (reader.Read())
                    return GetOrderStatusFromReader(reader);
                else
                    return null;
            }
        }

        /// <summary>
        /// Deletes a order status
        /// </summary>
        public override bool DeleteOrderStatus(int orderStatusId)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_DeleteOrderStatus", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@OrderStatusId", SqlDbType.Int).Value = orderStatusId;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        /// <summary>
        /// Updates a order status
        /// </summary>
        public override bool UpdateOrderStatus(OrderStatus orderStatus)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_UpdateOrderStatus", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@OrderStatusId", SqlDbType.Int).Value = orderStatus.Id;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = orderStatus.Title;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        /// <summary>
        /// Creates a new order status
        /// </summary>
        public override int InsertOrderStatus(OrderStatus orderStatus)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_InsertOrderStatus", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = orderStatus.AddedDate;
                cmd.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = orderStatus.AddedBy;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = orderStatus.Title;
                cmd.Parameters.Add("@OrderStatusId", SqlDbType.Int).Direction = ParameterDirection.Output;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (int)cmd.Parameters["@OrderStatusId"].Value;
            }
        }

        /// <summary>
        /// Returns a collection with all the shipping methods
        /// </summary>
        public override List<ShippingMethod> GetShippingMethods()
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_GetShippingMethods", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cn.Open();
                return GetShippingMethodCollectionFromReader(ExecuteReader(cmd));
            }
        }

        /// <summary>
        /// Returns an existing shipping method with the specified ID
        /// </summary>
        public override ShippingMethod GetShippingMethodById(Guid shippingMethodId)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_GetShippingMethodByID", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@ShippingMethodId", SqlDbType.UniqueIdentifier).Value = shippingMethodId;
                cn.Open();
                IDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
                if (reader.Read())
                    return GetShippingMethodFromReader(reader);
                else
                    return null;
            }
        }

        /// <summary>
        /// Deletes a shipping method
        /// </summary>
        public override bool DeleteShippingMethod(Guid shippingMethodId)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_DeleteShippingMethod", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@ShippingMethodId", SqlDbType.UniqueIdentifier).Value = shippingMethodId;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        /// <summary>
        /// Updates a shipping method
        /// </summary>
        public override bool UpdateShippingMethod(ShippingMethod shippingMethod)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_UpdateShippingMethod", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@ShippingMethodId", SqlDbType.UniqueIdentifier).Value = shippingMethod.Id;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = shippingMethod.Title;
                cmd.Parameters.Add("@Price", SqlDbType.Money).Value = shippingMethod.Price;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        /// <summary>
        /// Creates a new shipping method
        /// </summary>
        public override Guid InsertShippingMethod(ShippingMethod shippingMethod)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_InsertShippingMethod", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = shippingMethod.AddedDate;
                cmd.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = shippingMethod.AddedBy;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = shippingMethod.Title;
                cmd.Parameters.Add("@Price", SqlDbType.Money).Value = shippingMethod.Price;
                cmd.Parameters.Add("@ShippingMethodId", SqlDbType.UniqueIdentifier).Direction = ParameterDirection.Output;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (Guid)cmd.Parameters["@ShippingMethodId"].Value;
            }
        }

        /// <summary>
        /// Retrieves all products
        /// </summary>
        public override List<Product> GetProducts(string sortExpression, int pageIndex, int pageSize)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                sortExpression = EnsureValidProductsSortExpression(sortExpression);
                int lowerBound = pageIndex * pageSize + 1;
                int upperBound = (pageIndex + 1) * pageSize;
                string sql = string.Format(@"SELECT * FROM
(
   SELECT xz_Products.ProductId, xz_Products.AddedDate, xz_Products.AddedBy, xz_Products.CategoryId, xz_Products.Title, 
      xz_Products.Description, xz_Products.SKU, xz_Products.UnitPrice, xz_Products.DiscountPercentage, 
      xz_Products.UnitsInStock, xz_Products.SmallImageUrl, xz_Products.FullImageUrl, xz_Products.Votes, 
      xz_Products.TotalRating, xz_Categories.Title AS CategoryTitle, 
      ROW_NUMBER() OVER (ORDER BY {0}) AS RowNum
   FROM xz_Products INNER JOIN
      xz_Categories ON xz_Products.CategoryId = xz_Categories.CategoryId
) AllProducts
   WHERE AllProducts.RowNum BETWEEN {1} AND {2}
   ORDER BY RowNum ASC", sortExpression, lowerBound, upperBound);
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                List<Product> list = GetProductCollectionFromReader(ExecuteReader(cmd), false);
                return list;
            }
        }

        /// <summary>
        /// Returns the total number of products
        /// </summary>
        public override int GetProductCount()
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_GetProductCount", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cn.Open();
                int i = (int)ExecuteScalar(cmd);
                return i;// (int)ExecuteScalar(cmd);
            }
        }

        /// <summary>
        /// Retrieves all products for the specified department
        /// </summary>
        public override List<Product> GetProducts(Guid categoryId, string sortExpression, int pageIndex, int pageSize)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                sortExpression = EnsureValidProductsSortExpression(sortExpression);
                int lowerBound = pageIndex * pageSize + 1;
                int upperBound = (pageIndex + 1) * pageSize;
                string sql = string.Format(@"SELECT * FROM
(
   SELECT xz_Products.ProductId, xz_Products.AddedDate, xz_Products.AddedBy, xz_Products.CategoryId, xz_Products.Title, 
      xz_Products.Description, xz_Products.SKU, xz_Products.UnitPrice, xz_Products.DiscountPercentage, 
      xz_Products.UnitsInStock, xz_Products.SmallImageUrl, xz_Products.FullImageUrl, xz_Products.Votes, 
      xz_Products.TotalRating, xz_Categories.Title AS CategoryTitle, 
      ROW_NUMBER() OVER (ORDER BY {0}) AS RowNum
   FROM xz_Products INNER JOIN
      xz_Categories ON xz_Products.CategoryId = xz_Categories.CategoryId
   WHERE xz_Products.CategoryId = '{1}'
) CategoryProducts
   WHERE CategoryProducts.RowNum BETWEEN {2} AND {3}
   ORDER BY RowNum ASC", sortExpression, categoryId, lowerBound, upperBound);
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                return GetProductCollectionFromReader(ExecuteReader(cmd), false);
            }
        }

        /// <summary>
        /// Returns the total number of products for the specified department
        /// </summary>
        public override int GetProductCount(Guid categoryId)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_GetProductCountByCategory", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@CategoryId", SqlDbType.UniqueIdentifier).Value = categoryId;
                cn.Open();
                return (int)ExecuteScalar(cmd);
            }
        }

        /// <summary>
        /// Retrieves the product with the specified ID
        /// </summary>
        public override Product GetProductById(Guid productId)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_GetProductById", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@ProductId", SqlDbType.UniqueIdentifier).Value = productId;
                cn.Open();
                IDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
                if (reader.Read())
                    return GetProductFromReader(reader, true);
                else
                    return null;
            }
        }

        /// <summary>
        /// Retrieves the description for the product with the specified ID
        /// </summary>
        public override string GetProductDescription(Guid productId)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_GetProductDescription", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@ProductId", SqlDbType.UniqueIdentifier).Value = productId;
                cn.Open();
                return (string)ExecuteScalar(cmd);
            }
        }

        /// <summary>
        /// Deletes a product
        /// </summary>
        public override bool DeleteProduct(Guid productId)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_DeleteProduct", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@ProductId", SqlDbType.UniqueIdentifier).Value = productId;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        /// <summary>
        /// Inserts a new product
        /// </summary>
        public override Guid InsertProduct(Product product)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_InsertProduct", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = product.AddedDate;
                cmd.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = product.AddedBy;
                cmd.Parameters.Add("@CategoryId", SqlDbType.UniqueIdentifier).Value = product.CategoryId;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = product.Title;
                cmd.Parameters.Add("@Description", SqlDbType.NText).Value = product.Description;
                cmd.Parameters.Add("@SKU", SqlDbType.NVarChar).Value = product.SKU;
                cmd.Parameters.Add("@UnitPrice", SqlDbType.Money).Value = product.UnitPrice;
                cmd.Parameters.Add("@DiscountPercentage", SqlDbType.Int).Value = product.DiscountPercentage;
                cmd.Parameters.Add("@UnitsInStock", SqlDbType.Int).Value = product.UnitsInStock;
                cmd.Parameters.Add("@SmallImageUrl", SqlDbType.NVarChar).Value = product.SmallImageUrl;
                cmd.Parameters.Add("@FullImageUrl", SqlDbType.NVarChar).Value = product.FullImageUrl;
                cmd.Parameters.Add("@ProductId", SqlDbType.UniqueIdentifier).Direction = ParameterDirection.Output;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (Guid)cmd.Parameters["@ProductId"].Value;
            }
        }

        /// <summary>
        /// Updates an product
        /// </summary>
        public override bool UpdateProduct(Product product)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_UpdateProduct", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@ProductId", SqlDbType.UniqueIdentifier).Value = product.Id;
                cmd.Parameters.Add("@CategoryId", SqlDbType.UniqueIdentifier).Value = product.CategoryId;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = product.Title;
                cmd.Parameters.Add("@Description", SqlDbType.NText).Value = product.Description;
                cmd.Parameters.Add("@SKU", SqlDbType.NVarChar).Value = product.SKU;
                cmd.Parameters.Add("@UnitPrice", SqlDbType.Money).Value = product.UnitPrice;
                cmd.Parameters.Add("@DiscountPercentage", SqlDbType.Int).Value = product.DiscountPercentage;
                cmd.Parameters.Add("@UnitsInStock", SqlDbType.Int).Value = product.UnitsInStock;
                cmd.Parameters.Add("@SmallImageUrl", SqlDbType.NVarChar).Value = product.SmallImageUrl;
                cmd.Parameters.Add("@FullImageUrl", SqlDbType.NVarChar).Value = product.FullImageUrl;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        /// <summary>
        /// Inserts a vote for the specified product
        /// </summary>
        public override bool RateProduct(Guid productId, int rating)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_InsertVote", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@ProductId", SqlDbType.UniqueIdentifier).Value = productId;
                cmd.Parameters.Add("@Rating", SqlDbType.Int).Value = rating;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        /// <summary>
        /// Decrements the UnitsInStock field of the specified quantity for the specified product
        /// </summary>
        public override bool DecrementProductUnitsInStock(Guid productId, int quantity)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_DecrementUnitsInStock", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@ProductId", SqlDbType.UniqueIdentifier).Value = productId;
                cmd.Parameters.Add("@Quantity", SqlDbType.Int).Value = quantity;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        /// <summary>
        /// Retrieves the list of orders for the specified customer
        /// </summary>
        public override List<Order> GetOrders(string addedBy)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_GetOrdersByCustomer", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = addedBy;
                cn.Open();
                return GetOrderCollectionFromReader(ExecuteReader(cmd));
            }
        }

        /// <summary>
        /// Retrieves the list of orders in the specified state, and within the specified date range
        /// </summary>
        public override List<Order> GetOrders(int statusId, DateTime fromDate, DateTime toDate)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_GetOrdersByStatus", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@StatusId", SqlDbType.Int).Value = statusId;
                cmd.Parameters.Add("@FromDate", SqlDbType.DateTime).Value = fromDate;
                cmd.Parameters.Add("@ToDate", SqlDbType.DateTime).Value = toDate;
                cn.Open();
                return GetOrderCollectionFromReader(ExecuteReader(cmd));
            }
        }

        /// <summary>
        /// Retrieves the order with the specified ID
        /// </summary>
        public override Order GetOrderById(Guid orderId)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_GetOrderById", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@OrderId", SqlDbType.UniqueIdentifier).Value = orderId;
                cn.Open();
                IDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
                if (reader.Read())
                    return GetOrderFromReader(reader);
                else
                    return null;
            }
        }

        /// <summary>
        /// Deletes an order
        /// </summary>
        public override bool DeleteOrder(Guid orderId)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_Shop_DeleteOrder", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@OrderID", SqlDbType.UniqueIdentifier).Value = orderId;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        /// <summary>
        /// Inserts a new order
        /// </summary>
        public override Guid InsertOrder(Order order)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_Shop_InsertOrder", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = order.AddedDate;
                cmd.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = order.AddedBy;
                cmd.Parameters.Add("@StatusId", SqlDbType.Int).Value = order.StatusId;
                cmd.Parameters.Add("@ShippingMethod", SqlDbType.NVarChar).Value = order.ShippingMethod;
                cmd.Parameters.Add("@SubTotal", SqlDbType.Money).Value = order.SubTotal;
                cmd.Parameters.Add("@Shipping", SqlDbType.Money).Value = order.Shipping;
                cmd.Parameters.Add("@ShippingFirstName", SqlDbType.NVarChar).Value = order.ShippingFirstName;
                cmd.Parameters.Add("@ShippingLastName", SqlDbType.NVarChar).Value = order.ShippingLastName;
                cmd.Parameters.Add("@ShippingStreet", SqlDbType.NVarChar).Value = order.ShippingStreet;
                cmd.Parameters.Add("@ShippingPostalCode", SqlDbType.NVarChar).Value = order.ShippingPostalCode;
                cmd.Parameters.Add("@ShippingCity", SqlDbType.NVarChar).Value = order.ShippingCity;
                cmd.Parameters.Add("@ShippingState", SqlDbType.NVarChar).Value = order.ShippingState;
                cmd.Parameters.Add("@ShippingCountry", SqlDbType.NVarChar).Value = order.ShippingCountry;
                cmd.Parameters.Add("@CustomerEmail", SqlDbType.NVarChar).Value = order.CustomerEmail;
                cmd.Parameters.Add("@CustomerPhone", SqlDbType.NVarChar).Value = order.CustomerPhone;
                cmd.Parameters.Add("@CustomerFax", SqlDbType.NVarChar).Value = order.CustomerFax;
                cmd.Parameters.Add("@TransactionId", SqlDbType.NVarChar).Value = order.TransactionId;
                cmd.Parameters.Add("@OrderId", SqlDbType.UniqueIdentifier).Direction = ParameterDirection.Output;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (Guid)cmd.Parameters["@OrderId"].Value;
            }
        }

        /// <summary>
        /// Updates an existing order
        /// </summary>
        public override bool UpdateOrder(Order order)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                object shippedDate = order.ShippedDate;
                if (order.ShippedDate == DateTime.MinValue)
                    shippedDate = DBNull.Value;

                SqlCommand cmd = new SqlCommand("xz_WebShop_UpdateOrder", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@OrderId", SqlDbType.UniqueIdentifier).Value = order.Id;
                cmd.Parameters.Add("@StatusId", SqlDbType.Int).Value = order.StatusId;
                cmd.Parameters.Add("@ShippedDate", SqlDbType.DateTime).Value = shippedDate;
                cmd.Parameters.Add("@TransactionId", SqlDbType.NVarChar).Value = order.TransactionId;
                cmd.Parameters.Add("@TrackingId", SqlDbType.NVarChar).Value = order.TrackingId;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        /// <summary>
        /// Get a collection with all order items for the specified order
        /// </summary>
        public override List<OrderItem> GetOrderItems(Guid orderId)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_GetOrderItems", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@OrderId", SqlDbType.UniqueIdentifier).Value = orderId;
                cn.Open();
                return GetOrderItemCollectionFromReader(ExecuteReader(cmd));
            }
        }

        /// <summary>
        /// Inserts a new order item
        /// </summary>
        public override Guid InsertOrderItem(OrderItem orderItem)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("xz_WebShop_InsertOrderItem", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar).Value = ApplicationName;
                cmd.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = orderItem.AddedDate;
                cmd.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = orderItem.AddedBy;
                cmd.Parameters.Add("@OrderId", SqlDbType.Int).Value = orderItem.OrderId;
                cmd.Parameters.Add("@ProductId", SqlDbType.Int).Value = orderItem.ProductId;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = orderItem.Title;
                cmd.Parameters.Add("@SKU", SqlDbType.NVarChar).Value = orderItem.SKU;
                cmd.Parameters.Add("@UnitPrice", SqlDbType.Money).Value = orderItem.UnitPrice;
                cmd.Parameters.Add("@Quantity", SqlDbType.Int).Value = orderItem.Quantity;
                cmd.Parameters.Add("@OrderItemId", SqlDbType.UniqueIdentifier).Direction = ParameterDirection.Output;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (Guid)cmd.Parameters["@OrderItemId"].Value;
            }
        }
    }
}